/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.CityDao;
import cc.altius.model.City;
import cc.altius.model.State;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author santosh
 */
@Repository("CityDao")
public class CityDaoImpl implements CityDao {

    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    @Autowired
    private DataSource dataSource;
    Session session;
    Query query;

    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    @Transactional
    public List<State> getStateList() {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("State.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public List<City> getCityList() {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("City.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public Boolean addCity(City city) {

        String sql = "select * from city where CITY_NAME='" + city.getCityName()+"'";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map = jdbcTemplate.queryForList(sql);
        Boolean flag;
        if (map.size()!= 0) {
            flag = true;
            return flag;
        } else {
            flag=false;
            this.hibernateTemplate.save(city);
            return flag;
        }


    }

    @Override
    @Transactional
    public int editCity(City city) {

        String hql = "update City set cityName=:cityName, stateId.stateId=:stateId"
                + " where cityId=:cityId";

        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery(hql);
        query.setParameter("cityName", city.getCityName());
        query.setParameter("stateId", city.getStateId().getStateId());
        query.setParameter("cityId", city.getCityId());

        int result = query.executeUpdate();
        return result;
    }

    @Override
    @Transactional
    public City getCityById(int cityId) {
        Session session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(City.class);
        criteria.add(Restrictions.eq("cityId", cityId));
        return (City) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterCity(int stateId) {
        String sql = "SELECT city.CITY_ID, city.CITY_NAME, state.STATE_NAME "
                + "FROM city LEFT JOIN state on city.STATE_ID=state.STATE_ID WHERE 1 ";
        if (stateId != 0) {
            sql += " AND state.STATE_ID=" + stateId;
        }

        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional
    public City getCityByCityName(String cityName) {
        String hqlQuery = "from City city  WHERE city.cityName=:cityName";
        
        try {
            session=this.hibernateTemplate.getSessionFactory().getCurrentSession();
            query = session.createQuery(hqlQuery).setParameter("cityName", cityName);
            return (City) query.uniqueResult();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
